import pandas as pd
import time
import pymysql


df = pd.read_excel("../data/data.xlsx", sheet_name='1')

df['mid'] = df['mid'].map(lambda x: x.split("/")[-1][3:])
df['mid'] = df['mid'].astype(int)

df['play'] = df['play'].str.replace("\\n", " ")
df['play'] = df['play'].str.replace(" ", "")

df['create_time'] = df['create_time'].str.replace("\\n", " ")
df['create_time'] = df['create_time'].map(lambda x: x.strip(" "))

df['banger'] = df['banger'].str.replace("\\n", " ")
df['banger'] = df['banger'].str.replace(" ", "")

df['title'] = df['title'].str.replace(" ", "")

df['create_time'] = pd.to_datetime(df['create_time'])
df['create_time'] = df['create_time'].map(lambda x: x.timestamp())

df['up'] = df['up'].str.replace("\\n", " ")
df['up'] = df['up'].map(lambda x: x.strip(" "))

# df.to_excel('../data/c_data.xlsx', sheet_name='1', index=False, encoding='utf-8')


con = 'mysql+pymysql://root:root@127.0.0.1:3306/bili_tube?charset=utf8'
df.to_sql("into",con=con,index=False,if_exists='append')
# --fake initial
